"""
cron: */5 9-15 * * 1-5
new Env('股票数据采集');
"""
import requests
import os
import mysql.connector
from datetime import datetime

# 获取环境变量
host = os.environ.get('HOST')
user = os.environ.get('USER')
password = os.environ.get('PASSWORD')
database = os.environ.get('DATABASE')

print("正在获取环境变量...")
print(f"数据库地址：{host}")
print(f"数据库用户名：{user}")
print(f"数据库密码：{password}")
print(f"数据库名称：{database}")

# 要监控的股票列表
stocks_to_monitor = [
    {'code': 'sh000001', 'name': '上证指数'},
    {'code': 'sz399001', 'name': '深证成指'},
    {'code': 'sh601318', 'name': '中国平安'},
    {'code': 'sh600519', 'name': '贵州茅台'},
    # 可以添加更多股票
]

# 发送HTTP请求获取股票数据
print("发送HTTP请求获取股票数据...")
headers = {
    'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/91.0.4472.124 Safari/537.36',
    'Referer': 'http://finance.sina.com.cn/'
}

# 连接数据库
connection = mysql.connector.connect(
    host=host,
    user=user,
    password=password,
    database=database
)

cursor = connection.cursor()

# 创建表（如果不存在）
create_table_query = """
CREATE TABLE IF NOT EXISTS ningm_stock (
    id INT AUTO_INCREMENT PRIMARY KEY,
    stock_code VARCHAR(20) NOT NULL,
    stock_name VARCHAR(50) NOT NULL,
    current_price DECIMAL(10,2),
    change_amount DECIMAL(10,2),
    change_percent DECIMAL(5,2),
    volume BIGINT,
    turnover DECIMAL(20,2),
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_stock_code (stock_code),
    INDEX idx_create_time (create_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
"""
cursor.execute(create_table_query)

# 删除旧数据（保留7天内的数据）
print("删除旧数据...")
delete_query = "DELETE FROM ningm_stock WHERE create_time < DATE_SUB(NOW(), INTERVAL 7 DAY)"
cursor.execute(delete_query)

# 获取并保存股票数据
print("开始获取股票数据...")

for stock in stocks_to_monitor:
    try:
        # 新浪股票API
        url = f"http://hq.sinajs.cn/list={stock['code']}"
        response = requests.get(url, headers=headers)
        
        if response.status_code == 200:
            # 解析数据
            data = response.text.split('="')[1].split(',')
            if len(data) > 3:
                current_price = float(data[3])
                prev_close = float(data[2])
                change_amount = current_price - prev_close
                change_percent = (change_amount / prev_close) * 100
                volume = int(data[8])
                turnover = float(data[9])
                
                print(f"正在保存股票数据: {stock['name']} ({stock['code']})")
                insert_query = """
                    INSERT INTO ningm_stock 
                    (stock_code, stock_name, current_price, change_amount, change_percent, volume, turnover) 
                    VALUES (%s, %s, %s, %s, %s, %s, %s)
                """
                insert_values = (
                    stock['code'],
                    stock['name'],
                    current_price,
                    change_amount,
                    change_percent,
                    volume,
                    turnover
                )
                cursor.execute(insert_query, insert_values)
                
    except Exception as e:
        print(f"获取股票 {stock['code']} 的数据失败: {str(e)}")
        continue

connection.commit()
cursor.close()
connection.close()

print("数据保存完成！") 